package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.CarImgDto;

public class CarImageDaoImp extends TmsDao implements CarImageDao {

	protected CarImageDaoImp() throws TmsDaoException {
		super();
	}
	/**
	 * 批量保存图片信息
	 * @throws TmsDaoException 
	 */
	@Override
	public void insertCarImageInfo(List<CarImgDto> dtoList ) throws TmsDaoException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		CarImgDto dto = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_PIC);
			sql.append("(");
			sql.append(" CID, ");
			sql.append(" IMGTYPE, ");
			sql.append(" IMGKEY, ");
			sql.append(" IMGPATH, ");
			sql.append(" IMGSRC ");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,?,?,? )");

			ps = conn.prepareStatement(sql.toString());
			
			for(int i=0;i<dtoList.size();i++)
			{
				dto = (CarImgDto)dtoList.get(i);
				ps.setInt(1, dto.getcId());
				ps.setInt(2, Integer.parseInt(dto.getImgType()));
				ps.setInt(3, Integer.parseInt(dto.getImgKey()));
				ps.setString(4, dto.getImgPath());
				ps.setInt(5,dto.getImgSrc());
				ps.executeUpdate();

			}
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		
	}

	/**
	 * 根据车辆ID查询出车辆图片信息
	 * @throws TmsDaoException 
	 */
	@Override
	public List<CarImgDto> findAllCarImgInfoBySerial(int cId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		CarImgDto dto = null;
		List<CarImgDto> list = new ArrayList<CarImgDto>();
		
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("IMGTYPE, ");
			sql.append("IMGKEY,  ");
			sql.append("IMGPATH  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_PIC);
			sql.append(" WHERE ");
			sql.append(" CID=? ");
			sql.append(" AND ");
			sql.append(" IMGSRC=1 ");

			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, cId);
			
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new CarImgDto();
				dto.setImgKey(rs.getString("IMGKEY"));
				dto.setImgType(rs.getString("IMGTYPE"));
				dto.setImgPath(rs.getString("IMGPATH"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}

	/**
	 * 根据车辆的id值和图片的key值查询单条图片的信息
	 * @return
	 * @throws TmsDaoException
	 */
	@Override
	public CarImgDto findDtoByIdAndKey(int cId,String imgKey) throws TmsDaoException {
		CarImgDto imgDto = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("IMGTYPE, ");
			sql.append("IMGKEY,  ");
			sql.append("IMGPATH  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_PIC);
			sql.append(" WHERE ");
			sql.append(" CID=? ");
			sql.append(" AND ");
			sql.append(" IMGKEY=? ");
			sql.append(" AND ");
			sql.append(" IMGSRC=1 ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, cId);
			ps.setString(2, imgKey);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {	
				imgDto = new CarImgDto();
				imgDto.setImgKey(rs.getString("IMGKEY"));
				imgDto.setImgType(rs.getString("IMGTYPE"));
				imgDto.setImgPath(rs.getString("IMGPATH"));				
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		
		return imgDto;
	}
    /**
     * 更新车辆信息
     */
	@Override
	public void updateCarImageInfo(CarImgDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = super.getConnection();			
			StringBuffer sql = new StringBuffer();
			sql.append("UPDATE ");
			sql.append(TmsTableNames.TMS_PIC);
			sql.append(" SET ");			
			sql.append("IMGPATH=? ");
			sql.append(" WHERE ");
			sql.append(" CID=? ");
			sql.append(" AND ");
			sql.append(" IMGKEY=? ");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getImgPath());
			ps.setInt(2, dto.getcId());
			ps.setString(3, dto.getImgKey());
			
			// 执行sql
			rs = ps.executeQuery();
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		
	}
	
	/**
	 * 根据图片的图片状态查询图片信息
	 */
	@Override
	public List<CarImgDto> getCarImage(int cid,int src, int imgType)
			throws TmsDaoException {
		List<CarImgDto> imgDto = new ArrayList<CarImgDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("IMGTYPE, ");
			sql.append("IMGKEY,  ");
			sql.append("IMGPATH  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_PIC);
			sql.append(" WHERE ");
			sql.append(" CID=? ");
			sql.append(" AND ");
			sql.append(" IMGTYPE=? ");
			sql.append(" AND ");
			sql.append(" IMGSRC=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, cid);
			ps.setInt(2, imgType);
			ps.setInt(3, src);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {	
				CarImgDto dto = new CarImgDto();
				dto.setImgKey(rs.getString("IMGKEY"));
				dto.setImgType(rs.getString("IMGTYPE"));
				dto.setImgPath(rs.getString("IMGPATH"));
				imgDto.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		
		return imgDto;
	}
	
	/**
	 * 更新车辆图片信息
	 */
	@Override
	public void updateImage(CarImgDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = super.getConnection();			
			StringBuffer sql = new StringBuffer();
			sql.append("UPDATE ");
			sql.append(TmsTableNames.TMS_PIC);
			sql.append(" SET ");			
			sql.append("IMGPATH=? ");
			sql.append(" WHERE ");
			sql.append(" CID=? ");
			sql.append(" AND ");
			sql.append(" IMGKEY=? ");
			sql.append(" AND ");
			sql.append(" IMGTYPE=? ");
			sql.append(" AND ");
			sql.append(" IMGSRC=? ");
			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getImgPath());
			ps.setInt(2, dto.getcId());
			ps.setString(3, dto.getImgKey());
			ps.setString(4, dto.getImgType());
			ps.setInt(5, dto.getImgSrc());
			// 执行sql
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		
		
	}
	
	/**
	 * 根据车辆id、图片类型、图片key值取得图片路径
	 */
	@Override
	public String imgPath(int cid,String imgType,String imgKey,int imgsrc) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		String imgPath="";
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("IMGPATH  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_PIC);
			sql.append(" WHERE ");
			sql.append(" CID=? ");
			sql.append(" AND ");
			sql.append(" IMGTYPE=? ");
			sql.append(" AND ");
			sql.append(" IMGKEY=? ");
			sql.append(" AND ");
			sql.append(" IMGSRC=? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, cid);
			ps.setString(2, imgType);
			ps.setString(3,imgKey);
			ps.setInt(4, imgsrc);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {	
				imgPath=rs.getString("IMGPATH");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		
		return imgPath;
	}
	/**
	 * 
	 * 来源为3的车辆信息
	 */
	@Override
	public List<CarImgDto> getCarImgInfoByCId3(int cId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		CarImgDto dto = null;
		List<CarImgDto> list = new ArrayList<CarImgDto>();
		
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("IMGTYPE, ");
			sql.append("IMGKEY,  ");
			sql.append("IMGPATH  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_PIC);
			sql.append(" WHERE ");
			sql.append(" CID=? ");
			sql.append(" AND ");
			sql.append(" IMGSRC=3  ");

			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, cId);
			
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new CarImgDto();
				dto.setImgKey(rs.getString("IMGKEY"));
				dto.setImgType(rs.getString("IMGTYPE"));
				dto.setImgPath(rs.getString("IMGPATH"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}
	
	

}
